const Pool = require('../Tools/TestCenterPool').pool;
var execTrans = require('../Tools/TestCenterPool').execTrans;
const _ = require('lodash');

class UserModel {

  async pageList(condition) {
    try {
      let sql = "SELECT id, user_name, real_name, role_id, role_name FROM sys_user where 1=1";
      if (condition || !_.isUndefined(condition) || !condition.page || !condition.size) {
        if (condition.id) {
          sql += ` and id=${condition.id} `;
        }
        if (condition.userName) {
          sql += " and user_name like '%" + condition.userName + "%'";
        }
        if (condition.realName) {
          sql += " and real_name like '%" + condition.realName + "%'";
        }
      }
      sql += " ORDER BY create_time";
      sql += ` limit ${(condition.page - 1) * condition.size}, ${condition.size}`;

      let rows = await Pool.queryAsync(sql);
      let users = rows.map((row) => {
        return {
          id: row.id,
          userName: row.user_name,
          realName: row.real_name,
          roleId: row.role_id,
          roleName: row.role_name
        }
      })
      return users;
    } catch (error) {
      throw error;
    }
  }

  async count(condition) {
    try {
      let sql = "SELECT count(*) FROM sys_user where 1=1";

      if (condition) {
        if (condition.id) {
          sql += ` and id=${condition.id} `;
        }
        if (condition.userName) {
          sql += ` and user_name like '%${condition.userName}%' `;
        }
        if (condition.realName) {
          sql += ` and real_name like '%${condition.realName}%'`;
        }
      }
      let result = await Pool.queryAsync(sql);
      let count = result.map((r) => {
        return r['count(*)'];
      })[0];
      return count;
    } catch (error) {
      throw error;
    }
  }

  async query(condition) {
    
    try {
      let sql = "SELECT id, user_name, real_name, role_id, role_name, email, mobile FROM sys_user where 1=1 ";
      if (condition.id) {
        sql += ` and id=${condition.id} `;
      }
      if (condition.userName) {
        sql += ` and user_name like '%${condition.userName}%'`;
      }
      if (("realName" in condition) && !_.isUndefined(condition.realName)) {
        sql += ` and real_name like '%${condition.realName}%' `;
      }
      if (condition.roleId) {
        sql += ` and role_id=${condition.roleId} `;
      }
      sql += " ORDER BY  user_name";

      let rows = await Pool.queryAsync(sql);
      let users = rows.map((row) => {
        return {
          id: row.id,
          userName: row.user_name,
          realName: row.real_name,
          roleId: row.role_id,
          roleName: row.role_name,
          mobile:row.mobile,
          email:row.email
        }
      })
      return users;
    } catch (error) {
      throw error;
    }
  }

  async queryByUserName(userName) {
    try {
      let sql = `SELECT * FROM sys_user where user_name='${userName}'`;
      let user = await Pool.queryAsync(sql);
      if (!_.isEmpty(user)) {
        user = user.map((row) => {
          return {
            id: row.id,
            userName: row.user_name,
            realName: row.real_name,
            roleId: row.role_id,
            roleName: row.role_name,
            password: row.password
          }
        })[0];
        return user;
      } else {
        return [];
      }
    } catch (error) {
      throw error;
    }
  }

  async insert(user) {
    let sql = `INSERT INTO sys_user
      (user_name, real_name, password, mobile , email , role_id, role_name)
      values (?, ?, ?, ?, ?, ?, (select name from sys_role where id=?))`
    let sql_params = new Array(7);

    sql_params[0] = user.userName;
    sql_params[1] = user.realName;
    sql_params[2] = user.password;
    sql_params[3] = "mobile" in user ? user.mobile : '';
    sql_params[4] = "email" in user ? user.email : '';
    sql_params[5] = user.roleId ? user.roleId : 0;
    sql_params[6] = user.roleId ? user.roleId : 0;
    try {
      let result = await Pool.queryAsync(sql, sql_params);
      return result.affectedRows == 1 ? result.insertId : 0;
    } catch (error) {
      throw error;
    }
  }

  async update(user) {
    try {
      let update_user_sql = "UPDATE sys_user SET ";
      if (user.mobile) {
        update_user_sql += `mobile='${user.mobile}', `;
      }
      if (user.email) {
        update_user_sql += `email='${user.email}', `;
      }
      if (user.password) {
        update_user_sql += `password='${user.password}',`
      }
      if (user.roleId) {
        update_user_sql += `role_id=${user.roleId}, role_name = (select name from sys_role where id=${user.roleId} ),`;
      }
      // console.log('------------', update_user_sql);
      update_user_sql = update_user_sql.substring(0, update_user_sql.lastIndexOf(','));
      update_user_sql += ` WHERE id=${user.id}`;
      let result = await Pool.queryAsync(update_user_sql);

      return result.affectedRows == 1 ? true : false;
    } catch (error) {
      throw error;
    }
  }

  async deleteById(id) {
    let user_sql = `DELETE FROM sys_user where id=${id}`;
    let test_re_project_user_sql = `DELETE FROM test_re_project_user where user_id=${id}`;
    let test_re_record_user_sql = `DELETE FROM test_re_record_user where user_id=${id};`
    try {
      var sqlParams = [];
      sqlParams.push({sql:user_sql});
      sqlParams.push({sql:test_re_project_user_sql});
      sqlParams.push({sql :test_re_record_user_sql});
      await execTrans(sqlParams);
      return true
    } catch (error) {
      throw error;
    }
  }

  async insertUsersToProject(projectId, users) {
    try {
      users = _.uniqBy(users, "id");
      let values = users.map((user) => {
        return `(${projectId}, ${user.id})`;
      })
      values = _.join(values, ',');
      let sql = `INSERT INTO test_re_project_user (project_id, user_id) VALUES  ${values}`;
      let delete_sql = `DELETE FROM test_re_project_user WHERE project_id=${projectId} `;
      var sqlParams = [];
      sqlParams.push({sql:delete_sql});
      if (!_.isEmpty(users)) {
        sqlParams.push({sql:sql});
      }
      await execTrans(sqlParams);
      return true;
    } catch (error) {
      throw error;
    } 
  }

  async queryIsUserInProject(projectId, userId) {
    try {
      let sql = `SELECT user_id  FROM test_re_project_user WHERE project_id=${projectId} AND user_id=${userId}`;
      let result = await Pool.queryAsync(sql);
      let flag = false;
      if (!_.isEmpty(result)) {
        flag = true;
      }
      return flag;
    } catch (error) {
      throw error;
    }
  }

  async queryUsersByProjectId(projectId) {
    try {
      let re_project_user_sql = `SELECT rp.user_id,u.user_name,u.real_name,u.role_id,u.role_name,u.mobile
       FROM test_re_project_user AS rp,sys_user AS u WHERE rp.project_id=${projectId} AND rp.user_id=u.id`;

      let users = await Pool.queryAsync(re_project_user_sql);
      let pm = [];
      let developer = [];
      let tester = [];
      users.map((user) => {
        let userInfo = {
          id: user.user_id,
          userName: user.user_name,
          realName: user.real_name,
          roleId: user.role_id,
          roleName: user.role_name,
          mobile:user.mobile
        }
        if (userInfo.roleId == 1) {
          pm.push(userInfo);
        } else if (userInfo.roleId == 2) {
          developer.push(userInfo);
        } else if (userInfo.roleId == 3) {
          tester.push(userInfo);
        }
        return userInfo;
      });
      return { pm: pm, developer: developer, tester: tester };
    } catch (error) {
      throw error;
    }
  }

  async queryProjectsByUserId(userId){
    try {
      let sql = `SELECT p.id, p.name
      FROM test_re_project_user AS pu,test_project AS p 
      WHERE pu.project_id=p.id AND pu.user_id=${userId}`;
      
      let projects = await Pool.queryAsync(sql);
      projects = projects.map((project) => {
        return {
          id: project.id,
          name: project.name
        }
      })
      return projects;
    } catch (error) {
      throw error;
    }
  }

  async getDynamiclogTableSql(){

    let sql=`SELECT * from dynamic_log ORDER BY id DESC;`;

    let data =await Pool.queryAsync(sql);
    
    let newDataAll=data.map((row,index,arry)=>{
     
      
      return ({
        real_name:row.real_name,
        acition:row.acition,
        project_name:row.project_name,
        option_object:row.option_object,
        CreationTime:row.CreationTime

      }
    
    )})
  
    return  newDataAll
 
  }


}

module.exports = new UserModel();